<?php

namespace app\models;

use yii;
use yii\db\Query;

class Invoice_new extends BaseModel
{

    public function __construct()
    {
        $this->table = yii::$app->db->tablePrefix . 'invoice_new';
    }

    public function all($where = [], $offset = false, $limit = false, $order = false)
    {
        $db = new Query();
        $db->select('*')->from($this->table);
        if (!empty($where['staff'])) $db->andWhere(['saleman_num' => $where['staff']]);
        if (!empty($where['invoice'])) $db->andWhere(['invoice' => $where['invoice']]);
        if (!empty($where['begin_time'])) $db->andWhere(['>=', 'date', $where['begin_time']]);
        if (!empty($where['end_time'])) $db->andWhere(['<=', 'date', $where['end_time']]);
        if ($offset) $db->offset($offset);
        if ($limit) $db->limit($limit);
        if ($order) $db->orderBy($order);

        return $db->all();
    }

    public function one($where = [])
    {
        $db = new Query();
        $db->select('*')->from($this->table);
        if (!empty($where['id'])) $db->andWhere(['id' => $where['id']]);
        if (!empty($where['invoice'])) $db->andWhere(['invoice' => $where['invoice']]);
        return $db->one();

    }

    public function generateNum()
    {
        $date = date('Y-m-d');
        $db = new Query();
        $re = $db->select('count(id) as num')
            ->from($this->table)
            ->where(['date' => $date])
            ->one();
        $num = $re ? $re['num'] : 0;
        $num++;
        $orderNum = 'IN' . date('ymd') . str_pad($num, 3, '0', STR_PAD_LEFT);
        return $orderNum;

    }


    public function getList($where = [])
    {
        $db = new Query();
        $db->select('*')->from($this->table);
        if (!empty($where['job_number'])) {
            $db->andWhere(['saleman_num' => $where['job_number']]);
        }
        if (!empty($where['begin_date'])) {
            $db->andWhere(['>=', 'date', $where['begin_date']]);
        }
        if (!empty($where['end_date'])) {
            $db->andWhere(['<=', 'date', $where['end_date']]);
        }
        if (!empty($where['is_jiesuan'])) {
            $db->andWhere(['is_jiesuan'=>$where['is_jiesuan']]);
        }
        if (!empty($where['confirm_begin_date'])) {
            $db->andWhere(['>=', 'confirm_date', $where['confirm_begin_date']]);
        }
        if (!empty($where['confirm_end_date'])) {
            $db->andWhere(['<=', 'confirm_date', $where['confirm_end_date']]);
        }
        if (!empty($where['customer_name'])) {
            $db->andWhere(['like', 'customer_name', $where['customer_name']]);
        }
        if (!empty($where['invoice'])) {
            $db->andWhere(['invoice' => $where['invoice']]);
        }
        if (!empty($where['invoice_status'])) {
            $db->andWhere(['invoice_status' => $where['invoice_status']]);
        }
        if (!empty($where['order_status'])) {
            $db->andWhere(['order_status' => $where['order_status']]);
        }
        if (!empty($where['purchase'])) {
            $db->andWhere(['purchase' => $where['purchase']]);
        }
        if (!empty($where['recommend'])) {
            $db->andWhere(['recommend' => $where['recommend']]);
        }
        $db->andWhere(['!=', 'invoice', '']);
        $list = $db->orderBy(['date' => SORT_DESC])->all();
        return $list;
    }

    public function get_invoice_by_job_number($job_number)
    {
        $db = new Query();
        $list = $db->select('invoice')
            ->from($this->table)
            ->where(['in', 'saleman_num', $job_number])
            ->all();
        $arr = [];
        foreach ($list as $v) {
            $arr[] = $v;
        }
        return $arr;
    }

    public function get_invoice_info_by_invoice($invoice)
    {
        $db = new Query();
        $info = $db->select('a.customer_name,b.country,a.invoice')
            ->from($this->table . " as a")
            ->where(['invoice' => $invoice])
            ->join('left join', 'jingsourcing_customer as b', 'a.customer_num=b.number')
            ->one();
        return $info;
    }

    //通过月份获取invoice信息
    public function get_invoice_by_month($m)
    {
        $db = new Query();
        $list = $db->select('*')->from($this->table)
            ->where(["date_format(date,'%Y-%m')" => $m])
            ->andWhere(['!=', 'invoice', ''])
            ->orderBy(['date' => SORT_DESC])
            ->all();
        return $list;
    }

    /**按年份统计金额
     * @param $year
     * @return array
     */
    public function getSumByYear($year)
    {
        $dateBegin = $year . '-01-01';
        $dateEnd = $year . '-12-31';
        $db = new Query();
        return $db->select(['count(id) as num', "date_format(date,'%Y-%m') as m", 'avg(order_pay) as money', 'min(order_pay) as min_money', 'max(order_pay) as max_money'])->from($this->table)
            ->where(['>=', 'date', $dateBegin])
            ->andWhere(['<=', 'date', $dateEnd])
            ->andWhere(['!=', 'invoice', ''])
            ->groupBy('m')->all();
    }

    /**根据年份获取订单金额
     * @param $year
     * @return array
     */
    public function getOrderPayByYear($year, $where = [])
    {
        $dateBegin = $year . '-01-01';
        $dateEnd = $year . '-12-31';
        $db = new Query();
        $db->select(['id', "date_format(date,'%Y-%m') as m", 'order_pay', 'saleman_num', 'saleman'])->from($this->table);
        if (!empty($where['saleman_num'])) $db->andWhere(['saleman_num' => $where['saleman_num']]);
        $list = $db->andWhere(['>=', 'date', $dateBegin])
            ->andWhere(['<=', 'date', $dateEnd])
            ->andWhere(['!=', 'invoice', ''])
            ->orderBy(['date' => SORT_DESC])
            ->all();
        return $list;
    }

    /**获取信息
     * @param $in
     * @return array|bool
     */
    public function getDetailByInvoice($in)
    {
        $db = new Query();
        $info = $db->select('*')
            ->from($this->table)
            ->where(['invoice' => $in])
            ->limit(1)
            ->one();
        return $info;
    }

    /**
     * @return array
     */
    public function notUseInvoice()
    {
        $sql = 'SELECT * 
          FROM (
          SELECT invocie_num
          FROM jingsourcing_income
          WHERE invocie_num !=  \'\'
          GROUP BY invocie_num
          )a
          LEFT JOIN jingsourcing_invoice_new b ON a.invocie_num = b.invoice
          WHERE id IS NULL ';
        $list = yii::$app->db->createCommand($sql)->queryAll();
        return $list;
    }

    public function getSameInvoice()
    {
        $sql = 'SELECT invoice
            FROM (
            SELECT COUNT( invoice ) AS num, invoice
            FROM  `jingsourcing_invoice_new` 
            GROUP BY invoice
            )a
            WHERE num >=2';
        return yii::$app->db->createCommand($sql)->queryAll();
    }

    /**
     * @param $month
     * @return array
     */
    public function getInvoiceNum($month)
    {
        $db = new Query();
        $db->select('count(id) as num,saleman_num')->from($this->table);
        $db->andWhere(['between', 'date', $month . '-01', $month . '-31']);
        $list = $db->groupBy('saleman_num')->all();
        return $list;
    }


}
